﻿Imports System.Data.OleDb

Public Class QuizScoreManagement
    Inherits ConnectionClass

    Public Sub InsertQuizScore(ByVal quizId As Int32, ByVal userId As Int32, ByVal quizScoreScore As Int32)
        Try


        
        myConnection.Open()
        Dim SqlString As String = "INSERT INTO QuizesScores(QuizId,UserId, QuizScoreScore) VALUES(@f1,@f2,@f3)"
        Dim newCmd As OleDbCommand = New OleDbCommand(SqlString, myConnection)
        newCmd.CommandType = CommandType.Text
        newCmd.Parameters.AddWithValue("@f1", quizId)
        newCmd.Parameters.AddWithValue("@f2", userId)
        newCmd.Parameters.AddWithValue("@f3", quizScoreScore)

        newCmd.ExecuteNonQuery()
            myConnection.Close()
        Catch ex As Exception

        End Try
    End Sub

    Public Function SelectEasyScoreQuiz() As OleDbDataReader

        Try

        
        myConnection.Open()
        Dim SqlString As String = "SELECT  Quizes.QuizId, Quizes.UserId, Quizes.QuizName, Quizes.QuizDescription, Quizes.QuizDate, Quizes.QuizPicture, AVG(QuizesScores.QuizScoreScore) AS AverScore FROM            (Quizes INNER JOIN QuizesScores ON Quizes.QuizId = QuizesScores.QuizId) GROUP BY Quizes.QuizId, Quizes.UserId, Quizes.QuizName, Quizes.QuizDescription, Quizes.QuizDate, Quizes.QuizPicture ORDER BY AVG(QuizesScores.QuizScoreScore) ASC"
        Dim cmd As OleDbCommand = New OleDbCommand(SqlString, myConnection)
        cmd.CommandType = CommandType.Text

        Dim dr As OleDbDataReader = cmd.ExecuteReader()
        Return dr
        myConnection.Close()
        Catch ex As Exception
            Return Nothing
        End Try
    End Function

    Public Function SelectEasyScoreQuizByTagId(ByVal tagId As Int32) As OleDbDataReader
        Try

            myConnection.Open()
        Dim SqlString As String = "SELECT  Quizes.QuizId, Quizes.UserId, Quizes.QuizName, Quizes.QuizDescription, Quizes.QuizDate, Quizes.QuizPicture, AVG(QuizesScores.QuizScoreScore)  AS AverScore FROM            ((Quizes INNER JOIN QuizesScores ON Quizes.QuizId = QuizesScores.QuizId) INNER JOIN Quiz_Tags ON Quizes.QuizId = Quiz_Tags.QuizId) WHERE        (Quiz_Tags.TagId = @f1) GROUP BY Quizes.QuizId, Quizes.UserId, Quizes.QuizName, Quizes.QuizDescription, Quizes.QuizDate, Quizes.QuizPicture, Quiz_Tags.TagId ORDER BY AVG(QuizesScores.QuizScoreScore)"
        Dim cmd As OleDbCommand = New OleDbCommand(SqlString, myConnection)
        cmd.CommandType = CommandType.Text
        cmd.Parameters.AddWithValue("@f1", tagId)
        Dim dr As OleDbDataReader = cmd.ExecuteReader()
        Return dr
        myConnection.Close()
        Catch ex As Exception
            Return Nothing
        End Try
    End Function

    Public Function SelectHardScoreQuizByTagId(ByVal tagId As Int32) As OleDbDataReader

        Try


            myConnection.Open()
            Dim SqlString As String = "SELECT  Quizes.QuizId, Quizes.UserId, Quizes.QuizName, Quizes.QuizDescription, Quizes.QuizDate, Quizes.QuizPicture, AVG(QuizesScores.QuizScoreScore)  AS AverScore FROM            ((Quizes INNER JOIN QuizesScores ON Quizes.QuizId = QuizesScores.QuizId) INNER JOIN Quiz_Tags ON Quizes.QuizId = Quiz_Tags.QuizId) WHERE        (Quiz_Tags.TagId = @f1) GROUP BY Quizes.QuizId, Quizes.UserId, Quizes.QuizName, Quizes.QuizDescription, Quizes.QuizDate, Quizes.QuizPicture, Quiz_Tags.TagId ORDER BY AVG(QuizesScores.QuizScoreScore) DESC"
            Dim cmd As OleDbCommand = New OleDbCommand(SqlString, myConnection)
            cmd.CommandType = CommandType.Text
            cmd.Parameters.AddWithValue("@f1", tagId)
            Dim dr As OleDbDataReader = cmd.ExecuteReader()
            Return dr
            myConnection.Close()
        Catch ex As Exception
            Return Nothing
        End Try
    End Function

    Public Function SelectHardScoreQuiz() As OleDbDataReader
        Try

            myConnection.Open()
            Dim SqlString As String = "SELECT  Quizes.QuizId, Quizes.UserId, Quizes.QuizName, Quizes.QuizDescription, Quizes.QuizDate, Quizes.QuizPicture, AVG(QuizesScores.QuizScoreScore) AS AverScore FROM            (Quizes INNER JOIN QuizesScores ON Quizes.QuizId = QuizesScores.QuizId) GROUP BY Quizes.QuizId, Quizes.UserId, Quizes.QuizName, Quizes.QuizDescription, Quizes.QuizDate, Quizes.QuizPicture ORDER BY AVG(QuizesScores.QuizScoreScore) DESC"
            Dim cmd As OleDbCommand = New OleDbCommand(SqlString, myConnection)
            cmd.CommandType = CommandType.Text

            Dim dr As OleDbDataReader = cmd.ExecuteReader()
            Return dr
            myConnection.Close()

        Catch ex As Exception
            Return Nothing
        End Try
    End Function

End Class
